package haut.util.database;

import haut.data.SystemData;
import haut.system.member.events.SportingEvents;
import haut.system.member.person.Judge;
import haut.system.member.person.Player;
import haut.system.member.person.TeaAndStu;
import haut.system.member.person.Volunteer;
import haut.util.DuplicateCheck;
import haut.util.Time;
import haut.util.exception.DataException;
import haut.util.exception.DuplicationException;

import java.sql.*;
import java.util.Vector;

import static haut.system.member.person.Player.*;

/**
 * 数据库操作类
 */
abstract class HsqldbOperations {
    final String driver = "org.hsqldb.jdbcDriver";
    final String url = "jdbc:hsqldb:hsql://localhost";
    final String user = "sa";
    final String pass = "";
    Connection con;

    /** 连接数据库 */
    public void init() {
        if(con != null)
            return;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, pass);
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动程序失败！");
        } catch (SQLException e) {
            System.out.println("数据库连接失败！");
        }
    }

    /** 断开与数据库的连接 */
    public void close() {
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            System.out.println("无法断开与数据库的连接！");
        }
    }

    /** 获取Statement对象与数据库的连接 */
    public Statement createConnection() {
        try {
            return con.createStatement();
        } catch (SQLException e) {
            System.out.println("创建Statement对象失败！");
        }
        return null;
    }

    /** 获取PreparedStatement对象与数据库的连接 */
    public PreparedStatement createConnection(String sql) {
        try {
            return con.prepareStatement(sql);
        } catch (SQLException e) {
            System.out.println("创建preparedStatement对象失败！");
        }
        return null;
    }

    /** 创建表 */
    public void iniTable(String sql) {
        Statement stmt = null;
        stmt = createConnection();
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            System.out.println("创建数据表失败！");
        }
    }

    /** 向数据表插入数据
     * 
     * @param name 目标数据表
     * @param o 插入对象
     */
    public void insert(String name, Object o) {
        String sql = null;
        PreparedStatement pstmt = null;

        try {
            // 判断类o是哪个子类的实例，执行对应操作
            if(o instanceof SportingEvents) { // 插入运动项目信息
                /*sql = "insert into sportingEvents (eventID, eventName, time, place, rule, judgeID, judgeName, playerID, playerName)" +
                        " value (?, ?, ?, ?, ?, ?, ?, ?, ?)";
                pstmt = createConnection(sql);
                Vector<Player> playerVector = ((SportingEvents) o).getPlayer();
                for(Player player : playerVector) {
                    pstmt.setString(1, ((SportingEvents) o).getId());
                    pstmt.setString(2, ((SportingEvents) o).getName());
                    pstmt.setString(3, ((SportingEvents) o).getTime());
                    pstmt.setString(4, ((SportingEvents) o).getPlace());
                    pstmt.setString(5, ((SportingEvents) o).getRule());
                    pstmt.setString(6, ((SportingEvents) o).getJudge().getId());
                    pstmt.setString(7, ((SportingEvents) o).getJudge().getName());
                    pstmt.setString(8, player.getId());
                    pstmt.setString(9, player.getName());
                    pstmt.executeUpdate();
                }*/
                sql = "insert into sportingEvents (eventID, eventName, time, place, rule, judgeID, judgeName)" +
                        " value (?, ?, ?, ?, ?, ?, ?)";
                pstmt = createConnection(sql);
                pstmt.setString(1, ((SportingEvents) o).getId());
                pstmt.setString(2, ((SportingEvents) o).getName());
                pstmt.setString(3, ((SportingEvents) o).getTime());
                pstmt.setString(4, ((SportingEvents) o).getPlace());
                pstmt.setString(5, ((SportingEvents) o).getRule());
                pstmt.setString(6, ((SportingEvents) o).getJudge().getId());
                pstmt.setString(7, ((SportingEvents) o).getJudge().getName());
                pstmt.executeUpdate();
            } else if (o instanceof Player) { // 插入运动员信息
                sql = "insert into Player (playerID, playerName, eventID, eventName, score)" +
                        " value (?, ?, ?, ?)";
                Vector<Player.Events> eventsVector = ((Player) o).getMyEvents();
                for (Player.Events events : eventsVector) {
                    pstmt.setString(1, ((Player) o).getId());
                    pstmt.setString(2, ((Player) o).getName());
                    pstmt.setString(3, events.getEvents().getId());
                    pstmt.setString(4, events.getEvents().getName());
                    pstmt.setDouble(5, events.getScore());
                    pstmt.executeUpdate();
                }
            } else if (o instanceof Judge) { // 插入裁判员信息
                sql = "insert into Judge (judgeID, judgeName, eventID, eventName)" +
                        " value (?, ?, ?, ?)";
                Vector<Judge.Event> eventVector = ((Judge) o).getMyEvents();
                for(Judge.Event event : eventVector) {
                    pstmt.setString(1, ((Judge) o).getId());
                    pstmt.setString(2, ((Judge) o).getName());
                    pstmt.setString(3, event.getEvent().getId());
                    pstmt.setString(4, event.getEvent().getName());
                    pstmt.executeUpdate();
                }
            } else if (o instanceof Volunteer) { // 插入志愿者信息
                sql = "insert into Volunteer (volunteerID, volunteerName, eventID, eventName)" +
                        " value (?, ?, ?, ?)";
                Vector<SportingEvents> eventsVector = ((Volunteer) o).getMyEvents();
                for(SportingEvents event : eventsVector) {
                    pstmt.setString(1, ((Volunteer) o).getId());
                    pstmt.setString(2, ((Volunteer) o).getName());
                    pstmt.setString(3, event.getId());
                    pstmt.setString(4, event.getName());
                    pstmt.executeUpdate();
                }
            }
        } catch (SQLException e) {
            System.out.println("插入数据失败！");
        }
        try {
            pstmt.close();
        } catch (SQLException e) {
            System.out.println("断开对象连接失败！");
        }
    }

    /** 更改数据表中的某项数据
     * 这里是指修改运动员的成绩
     * @param eventId 目标项目
     * @param playerId 项目内的运动员id
     * @param score 更改后得分
     */
    public void update(String eventId, String playerId, double score) {
        String sql = "update player set score = ? where eventId = " + eventId + " and where playerID = " + playerId;
        PreparedStatement pstmt = null;
        try {
            pstmt.setDouble(1, score);
        } catch (SQLException e) {
            System.out.println("执行修改操作失败！");
        }
    }

    /** 读取数据表
     * 
     * @param name 目标数据表
     */
    public void getData(String name) throws DataException, DuplicationException {
        String sql = "select * from " + name;
        Statement stmt = createConnection();
        ResultSet res = null;
        SystemData systemData = SystemData.getInstance();

        try {
            res = stmt.executeQuery(sql);
            if ("SportingEvents".equals(name)) { // 读取项目数据
                while (res.next()) {
                    systemData.addAllEvent(new SportingEvents(res.getString("eventID"), res.getString("eventName"),
                            new Time("", "", ""),
                            res.getNString("place"), res.getString("rule")));
                }
            } else if ("Player".equals(name)) { // 读取运动员数据
                while (res.next()) {
                    String eventID = res.getString("eventID");
                    SportingEvents sportingEvents = null;
                    Vector<SportingEvents> allEvents = systemData.getAllEvents();
                    for(SportingEvents event : allEvents) {
                        if(event.getId().equals(eventID)) {
                            sportingEvents = event;
                            break;
                        }
                    }
                    Player player = new Player(
                            new TeaAndStu(res.getString("playerID"), res.getString("playerName")),
                            sportingEvents);
                    double score = res.getDouble("score");
                    try {
                        DuplicateCheck.playerCheck(systemData.getAllPlayer(), player);
                        // 如果运动员不存在，加入
                        // 加入项目
                        player.addEvent(sportingEvents, score);
                        systemData.addAllPlayer(player);
                    } catch (DuplicationException e) {
                        player.addEvent(sportingEvents);
                    }
                }
            } else if ("Judge".equals(name)) { // 读取裁判员数据
                while (res.next()) {
                    String eventID = res.getString("eventID");
                    SportingEvents sportingEvents = null;
                    Vector<SportingEvents> allEvents = systemData.getAllEvents();
                    for(SportingEvents event : allEvents) {
                        if(event.getId().equals(eventID)) {
                            sportingEvents = event;
                            break;
                        }
                    }
                    Judge judge = new Judge(
                            new TeaAndStu(res.getString("playerID"), res.getString("playerName")),
                            sportingEvents);
                    try {
                        DuplicateCheck.judgeCheck(systemData.getAllJudges(), judge);
                        // 如果裁判员不存在，加入
                        // 加入项目
                        judge.addEvent(sportingEvents);
                        systemData.addAllJudge(judge);
                    } catch (DuplicationException e) {
                        judge.addEvent(sportingEvents);
                    }
                }
            } else if ("Volunteer".equals(name)) { // 读取志愿者数据
                while (res.next()) {
                    String eventID = res.getString("eventID");
                    SportingEvents sportingEvents = null;
                    Vector<SportingEvents> allEvents = systemData.getAllEvents();
                    for(SportingEvents event : allEvents) {
                        if(event.getId().equals(eventID)) {
                            sportingEvents = event;
                            break;
                        }
                    }
                    Volunteer volunteer = new Volunteer(
                            new TeaAndStu(res.getString("playerID"), res.getString("playerName")),
                            sportingEvents);
                    try {
                        DuplicateCheck.volunteerCheck(systemData.getAllVolunteer(), volunteer);
                        // 如果裁判员不存在，加入
                        // 加入项目
                        volunteer.addEvent(sportingEvents);
                        systemData.addAllVolunteer(volunteer);
                    } catch (DuplicationException e) {
                        volunteer.addEvent(sportingEvents);
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("数据读取失败！");
        }
    }
}
